﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using SqlCon;

namespace yfxt
{
    public partial class ms_zyjz : Form
    {
        public ms_zyjz()
        {
            InitializeComponent();
        }

        private void button3_Click(object sender, EventArgs e)//调入病人的基本信息 zy_brry,gy_ygdm,gy_ksdm数据表
        {
            string s_zy = "select  zyhm,brxm,(case brxb when '1' then '男' when '2' then '女' end) as brxb," +
                           "(case brxz when '123' then '自费' when '122' then '医保' end )as brxz,ksmc,brch,ygxm " +
                           " from zy_brry,gy_ygdm,gy_ksdm where zy_brry.zyys=gy_ygdm.ygdm and zy_brry.brks=gy_ksdm.ksdm" +
                           " and zyhm='" + tb_zyhm.Text.ToString() + "' ";

            SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction);


            if (tb_zyhm.Text.Trim() == "")
            {
                MessageBox.Show("请输入住院号码！");
                return;
            }
            else
            {
                if (conn.State != ConnectionState.Open) { conn.Open(); }
                using (SqlCommand cmd = new SqlCommand(s_zy, conn))
                {


                    SqlDataReader dr = cmd.ExecuteReader();


                    if (!dr.HasRows)
                    {
                        MessageBox.Show("没有这个住院号码的相关信息，请认真检查后再重新输入！");
                        return;
                    }
                    else
                    {
                        while (dr.Read())//填充病人信息
                        {
                            tb_brxm.Text = dr["brxm"].ToString();
                            tb_brxb.Text = dr["brxb"].ToString();
                            tb_brxz.Text = dr["brxz"].ToString();
                            tb_ksmc.Text = dr["ksmc"].ToString();
                            tb_brch.Text = dr["brch"].ToString();
                            tb_zyys.Text = dr["ygxm"].ToString();
                        }
                    }
                    dr.Close();



                    string s_zybz = "select @zybz=zybz from zy_brry where zyhm=" + tb_zyhm.Text.ToString() + " ";//搜索记账标志标志 是否提交上去记账

                    using (SqlConnection coo = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
                    {
                        if (coo.State != ConnectionState.Open) { coo.Open(); }
                        SqlCommand cm = new SqlCommand(s_zybz, coo);

                        SqlParameter p = cm.Parameters.Add("@zybz", SqlDbType.NVarChar, 40);

                        p.Direction = ParameterDirection.Output;

                        cm.ExecuteNonQuery();

                        if (p.Value.ToString().Equals("1"))
                        {
                            MessageBox.Show("该住院号已经记过账了，不需要重复记账！");
                            return;
                        }
                    }








                    //yplx 药品类型 填充药品信息到datagridview

                    string s_ypxx = "select ypmc,ypgg,ypdw,ypdj,ycsl,sum(ycsl*ypdj)as hjje, " +
                                     " (case zfbz when '0' then '自费' when '1' then '医保' end ) as zfbz ,yk_typk.ypxh as ypxh" +
                                     " from yk_typk,zy_brry,zy_bqyz where zy_brry.zyh=zy_bqyz.zyh and zy_bqyz.ypxh=yk_typk.ypxh " +
                                     " and  yplx='1' and zy_brry.zyhm=" + tb_zyhm.Text.ToString() +
                                     " group by ypmc,ypgg,ypdw,ypdj,ycsl,zfbz,yk_typk.ypxh";

                    using (SqlConnection conn_01 = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
                    {

                        SqlDataAdapter da = new SqlDataAdapter(s_ypxx, conn_01);

                        DataTable dt = new DataTable();

                        da.Fill(dt);

                        dataGridView1.DataSource = dt;


                        dataGridView1.Columns[0].HeaderCell.Value = "药品名称";
                        dataGridView1.Columns[1].HeaderCell.Value = "药品规格";
                        dataGridView1.Columns[2].HeaderCell.Value = "药品单位";
                        dataGridView1.Columns[3].HeaderCell.Value = "药品单价";
                        dataGridView1.Columns[4].HeaderCell.Value = "药品数量";
                        dataGridView1.Columns[5].HeaderCell.Value = "单条药品合计金额";
                        dataGridView1.Columns[6].HeaderCell.Value = "药品性质";
                        dataGridView1.Columns[7].HeaderCell.Value = "药库编码";
                        decimal d = 0;
                        for (int i = 0; i < dataGridView1.Rows.Count; i++)
                        {

                            d = d + Convert.ToDecimal(dt.Rows[i][5].ToString());
                        }


                        tb_ypts.Text = dataGridView1.Rows.Count.ToString();

                        tb_hjje.Text = d.ToString();

                        tb_zfje.Text = d.ToString();
                    }



                }
            }







        }

        private void ms_zyjz_Load(object sender, EventArgs e)
        {

        }

        private void button2_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void tb_zyhm_KeyUp(object sender, KeyEventArgs e)//键盘事件 调用  button3_Click(sender, e)事件
        {
            if (e.KeyCode == Keys.Enter)//调用事件 回车调入病人信息
            {
                button3_Click(sender, e);
            }

        }

        private void button1_Click(object sender, EventArgs e)//记账按钮
        {
            if (tb_zyhm.Text.Trim().Equals("")) { MessageBox.Show("你没有输入住院号码！请输入！"); return; }

            string s = "select zyh from zy_brry where zyhm='" + tb_zyhm.Text.ToString() + " '";

            using (SqlConnection conn_01 = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
            {
                if (conn_01.State != ConnectionState.Open) { conn_01.Open(); }

                SqlCommand cmd = new SqlCommand(s, conn_01);

                SqlDataReader sdr = cmd.ExecuteReader();

                if (!sdr.HasRows)
                {

                    MessageBox.Show("当前所输入的住院号码不存在，重新输入！");
                }
            }

            string s_ = "select @zyh=zyh from zy_brry where zyhm='" + tb_zyhm.Text.ToString() + " '";//取得zyh字段 作为字段查询

            SqlParameter sp;
            using (SqlConnection conn_01 = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
            {
                if (conn_01.State != ConnectionState.Open) { conn_01.Open(); }

                SqlCommand cmd = new SqlCommand(s_, conn_01);

                sp = cmd.Parameters.Add("@zyh", SqlDbType.NVarChar, 40);

                sp.Direction = ParameterDirection.Output;

                cmd.ExecuteNonQuery();


            }



            string s_select_01 = "select @jlxh=max(jlxh) from zy_fymx";//取得最大的记录序号
            SqlParameter sp_01;

            using (SqlConnection conn_02 = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
            {
                if (conn_02.State != ConnectionState.Open) { conn_02.Open(); }

                SqlCommand cmd = new SqlCommand(s_select_01, conn_02);

                sp_01 = cmd.Parameters.Add("@jlxh", SqlDbType.NVarChar, 40);

                sp_01.Direction = ParameterDirection.Output;

                cmd.ExecuteNonQuery();





            }


            string s_select_02 = "select @ygdm=ygdm from gy_ygdm where ygxm='" + tb_zyys.Text.ToString() + " '";//取得医生代码
            SqlParameter sp_02;

            using (SqlConnection conn_03 = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
            {
                if (conn_03.State != ConnectionState.Open) { conn_03.Open(); }

                SqlCommand cmd = new SqlCommand(s_select_02, conn_03);

                sp_02 = cmd.Parameters.Add("@ygdm", SqlDbType.NVarChar, 40);

                sp_02.Direction = ParameterDirection.Output;

                cmd.ExecuteNonQuery();





            }


            string s_select_03 = "select @ksdm=ksdm from gy_ksdm where ksmc='" + tb_ksmc.Text.ToString() + " '";//取得科室代码
            SqlParameter sp_03;

            using (SqlConnection conn_04 = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
            {
                if (conn_04.State != ConnectionState.Open) { conn_04.Open(); }

                SqlCommand cmd = new SqlCommand(s_select_03, conn_04);

                sp_03 = cmd.Parameters.Add("@ksdm", SqlDbType.NVarChar, 40);

                sp_03.Direction = ParameterDirection.Output;

                cmd.ExecuteNonQuery();





            }



            string s_select_04 = "select @yzxh=max(yzxh) from zy_fymx";//取得医嘱序号
            SqlParameter sp_04;

            using (SqlConnection conn_05 = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
            {
                if (conn_05.State != ConnectionState.Open) { conn_05.Open(); }

                SqlCommand cmd = new SqlCommand(s_select_04, conn_05);

                sp_04 = cmd.Parameters.Add("@yzxh", SqlDbType.NVarChar, 40);

                sp_04.Direction = ParameterDirection.Output;

                cmd.ExecuteNonQuery();




            }

            if (MessageBox.Show("您是否确定住院记账？？", "温馨的提示", MessageBoxButtons.YesNoCancel) == DialogResult.Yes)
            {

                for (int rowcount = 0; rowcount < dataGridView1.Rows.Count; rowcount++)
                {
                    //进行记账 插入zy_fymx这样表
                    string s_insert = "insert into zy_fymx values " +
                                      "('" + (Convert.ToInt32(sp_01.Value.ToString()) + 1) + "','" + sp.Value.ToString() + "'" +
                                      "," + DateTime.Now.ToShortDateString() + ",'" + dataGridView1.Rows[rowcount].Cells["ypxh"].Value.ToString() + "'" +
                                      ",'" + dataGridView1.Rows[rowcount].Cells["ypmc"].Value.ToString() + "'," + "'0'" +
                                      "," + dataGridView1.Rows[rowcount].Cells["ycsl"].Value.ToString() + "," + dataGridView1.Rows[rowcount].Cells["ypdj"].Value.ToString() +
                                      "," + dataGridView1.Rows[rowcount].Cells["hjje"].Value.ToString() + "," + dataGridView1.Rows[rowcount].Cells["hjje"].Value.ToString() +
                                      ",'" + sp_02.Value.ToString() + "','" + sp_02.Value.ToString() + "'" +
                                      ",'" + sp_02.Value.ToString() + "','" + sp_03.Value.ToString() + "'" +
                                      ",'" + sp_03.Value.ToString() + "','" + sp_02.Value.ToString() + "'" +
                                      "," + DateTime.Now.ToShortDateString() + "," + "'2'" + "," + "'2'" +
                                      "," + "'2'" + "," + "'0'" +
                                      "," + "'1'" + ",'" + (Convert.ToInt32(sp_01.Value.ToString()) + 1) + "'" +
                                      "," + DateTime.Now.ToShortDateString() + "," + DateTime.Now.ToShortDateString() +
                                      "," + "'0'" + "," + "'4'" + "," + "'1'" + "," + "'0'" + ")";



                    using (SqlConnection conn_06 = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
                    {
                        if (conn_06.State != ConnectionState.Open) { conn_06.Open(); }


                        SqlCommand cmd = new SqlCommand(s_insert, conn_06);

                        cmd.ExecuteNonQuery();
                    }


                    string s_brry = "update zy_bryy set zybz=1 where zyhm=" + tb_zyhm.Text.ToString() + " ";

                    using (SqlConnection c1 = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
                    {
                        if (c1.State != ConnectionState.Open) { c1.Open(); }

                        SqlCommand cmd1 = new SqlCommand(s_brry, c1);

                        cmd1.ExecuteNonQuery();
                    }

                }

                MessageBox.Show("住院记账完成！");
            }
        }
    }
}
